# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter




# 连接数据库
# from a51job.items import A51JobItem,JobDescItem
# import pymysql
# class MysqlPipeline:
#     #读取配置文件，初始化连接以及游标
#     def open_spider(self,spider):
#         host=spider.settings.get("MYSQL_DB_HOST")
#         port = spider.settings.get("MYSQL_DB_PORT")
#         dbname = spider.settings.get("MYSQL_DB_NAME")
#         user = spider.settings.get("MYSQL_DB_USER")
#         pwd = spider.settings.get("MYSQL_DB_PASSWORD")
#         # 创建数据库连接
#         self.db_conn=pymysql.connect(host=host,port=port,db=dbname,user=user,password=pwd)    #创建数据库连接
#         # 打开游标
#         self.db_cur=self.db_conn.cursor()
#
#     #     处理数据。数据库的增删改查操作
#     def process_item(self, item, spider):
#         if isinstance(item, A51JobItem):
#             values=(
#                 item["p_name"],
#                 item["p_salary"],
#                 item["p_address"],
#                 item["p_edu"],
#                 item["p_time"],
#                 item["p_company"],
#                 item["p_additional"],
#                 item["job_href"]#与占位符%s对应的数据
#             )
#             # sql语句，数据部分使用占位符%s代替
#             sql="insert into post(p_name,p_salary,p_address,p_edu,p_time,p_company,p_additional,job_href) value (%s,%s,%s,%s,%s,%s,%s,%s);"    #插入数据
#             self.db_cur.execute(sql,values)   #执行SQL语句
#             self.conn.commit()
#         if isinstance(item, JobDescItem):
#             values = (
#                 item["content"]  # 与占位符%s对应的数据
#             )
#             # sql语句，数据部分使用占位符%s代替
#             sql = "insert into zhaopin(content) value (%s);"  # 插入数据
#             self.db_cur.execute(sql, values)  # 执行SQL语句
#             self.conn.commit()
#
#         return item
#
#     # 提交数据，释放资源
#     def close_spider(self, spider):
#         self.db_conn.commit()  # 提交事务
#         self.db_cur.close()  #关闭游标
#         self.db_conn.close() #关闭数据库连接

import pymysql
# import MySQLdb.cursors

class A51JobPipeline:
    def process_item(self, item, spider):
        return item


# class MysqlPipeline:
#     #读取配置文件，初始化连接以及游标
#     def open_spider(self,spider):
#         host=spider.settings.get("MYSQL_DB_HOST")
#         port = spider.settings.get("MYSQL_DB_PORT")
#         dbname = spider.settings.get("MYSQL_DB_NAME")
#         user = spider.settings.get("MYSQL_DB_USER")
#         pwd = spider.settings.get("MYSQL_DB_PASSWORD")
#         # 创建数据库连接
#         self.db_conn=pymysql.connect(host=host,port=port,db=dbname,user=user,password=pwd)    #创建数据库连接
#         # 打开游标
#         self.db_cur=self.db_conn.cursor()
#
#     #     处理数据。数据库的增删改查操作
#     # def process_item(self, item, spider):
#     #     if isinstance(item, A51JobItem):
#     #         values=(
#     #             item["p_name"],
#     #             item["p_salary"],
#     #             item["p_address"],
#     #             item["p_edu"],
#     #             item["p_time"],
#     #             item["p_company"],
#     #             item["p_additional"],
#     #             item["job_href"]  # 与占位符%s对应的数据
#     #         )
#     #         # sql语句，数据部分使用占位符%s代替
#     #         sql="insert into post(p_name,p_salary,p_address,p_edu,p_time,p_company,p_additional,job_href) value (%s,%s,%s,%s,%s,%s,%s,%s);"    #插入数据
#     #         self.db_cur.execute(sql,values)   #执行SQL语句
#     #         self.conn.commit()
#     #     if isinstance(item, JobDescItem):
#     #         values = (
#     #             item["content"]  # 与占位符%s对应的数据
#     #         )
#     #         # sql语句，数据部分使用占位符%s代替
#     #         sql = "insert into zhaopin(content) value (%s);"  # 插入数据
#     #         self.db_cur.execute(sql, values)  # 执行SQL语句
#     #         self.conn.commit()
#     #
#     #     return item
#     #
#     # # 提交数据，释放资源
#     # def close_spider(self, spider):
#     #     self.db_conn.commit()  # 提交事务
#     #     self.db_cur.close()  #关闭游标
#     #     self.db_conn.close() #关闭数据库连接

# import pymysql
# class MysqlPipeline:
#     def open_spider(self,spider):
#         host=spider.settings.get("MYSQL_DB_HOST")
#         port = spider.settings.get("MYSQL_DB_PORT")
#         dbname = spider.settings.get("MYSQL_DB_NAME")
#         user = spider.settings.get("MYSQL_DB_USER")
#         pwd = spider.settings.get("MYSQL_DB_PASSWORD")
#
#         self.db_conn=pymysql.connect(host=host,port=port,db=dbname,user=user,password=pwd)    #创建数据库连接
#         # 打开游标
#         self.db_cur=self.db_conn.cursor()
#     def process_item(self, item, spider):
#         values = (
#             item["p_name"],
#             item["p_salary"],
#             item["p_address"],
#             item["p_edu"],
#             item["p_time"],
#             item["p_company"],
#             item["p_additional"] # 与占位符%s对应的数据
#         )
#         # sql语句，数据部分使用占位符%s代替
#         sql="insert into post(p_name,p_salary,p_address,p_edu,p_time,p_company,p_additional) value (%s,%s,%s,%s,%s,%s,%s);"    #插入数据
#         self.db_cur.execute(sql,values)   #执行SQL语句
#         return item
#
#     def close_spider(self, spider):
#         self.db_conn.commit()  # 提交事务
#         self.db_cur.close()  #关闭游标
#         self.db_conn.close() #关闭数据库连接

class MySQLPipeline:
    def open_spider(self, spider):
        # 读取setting.pu配置
        host = spider.settings.get('MYSQL_DB_HOST')
        port = spider.settings.get('MYSQL_DB_POST')
        dbname = spider.settings.get('MYSQL_DB_NAME')
        user = spider.settings.get('MYSQL_DB_USER')
        pwd = spider.settings.get('MYSQL_DB_PASSWORD')

        # 创建数据库连接
        self.db_conn = pymysql.connect(host=host, port=port, db=dbname,
                                       user=user, password=pwd)

        # 打开游标
        self.db_cur = self.db_conn.cursor()

    def process_item(self, item, spider):
        values = (
                            item["p_name"],
                            item["p_salary"],
                            item["p_address"],
                            item["p_edu"],
                            item["p_time"],
                            item["p_company"],
                            item["p_additional"],
                            # item["job_href"]  # 与占位符%s对应的数据
                        )
        sql="insert into post(p_name,p_salary,p_address,p_edu,p_time,p_company,p_additional) value (%s,%s,%s,%s,%s,%s,%s);"
        print(sql)
        self.db_cur.execute(sql, values)
        self.db_conn.commit()
        return item

    def close_spider(self, spider):
        # self.db_conn.commit()
        self.db_cur.close()
        self.db_conn.close()
